import pandas as pd
#from openpyxl import load_workbook
#定义数据表路径
biao='../data/创业板指研究.xlsx'
biao1='../output/创业板指研究1.xlsx'

'''
df——源数据表
df1——
'''

df = pd.read_excel(biao, sheet_name='Sheet1')

df['last'] = (
    df['涨幅(%)'] +
    0.8 * df['涨幅(%)'].shift(1) +  # 取前一行的值
    0.5 * df['涨幅(%)'].shift(2)  # 取前两行的值
)

df.loc[0:2, 'last'] = 0  # 强制将前三行（索引0,1,2）设为0

#df['final']=(df['收盘价'].shift(-22)-df['收盘价'])/df['收盘价']*100
#df.loc[df['final'].isna().tail(23).index, 'final'] = 0

#df.to_excel(biao1, sheet_name='Sheet1', index=False)

last=df['last'].values
spj=df['收盘价'].values
n=0
m=0
s=1
ld=0
dcz=1
qs=2333
btr=[]
tr=[]
sb=[]
for i in range(len(last)):
    if last[i]>-2.9:
        btr.append(i)
    #if last[i]>9:
        #btr.append(i)
#print(len(btr),btr)
for i in range(qs,len(last)-45):
    f = 1
    d=0
    s_last =s
    if i in btr:
        continue
    else:
        tr.append(i)
    for j in range(1,5):
        if spj[i+j]>spj[i]*1.1:
            f=f*0
            s=s*(spj[i+j]*0.985/spj[i])
            d+=j
            break
    if f!=0:
        for j in range(6,10):
            if spj[i+j]>spj[i]*1.01:
                f=f*0
                s = s * (spj[i + j] / spj[i])
                d=d+j
                break
    if f!=0:
        for j in range(11,15):
            if spj[i+j]>spj[i]*1.01:
                f=f*0
                s = s * (spj[i + j] / spj[i] )
                d=d+j
                break
    if f != 0:
        for j in range(16, 20):
            if spj[i + j] > spj[i]*1.02:
                f = f * 0
                s = s *(spj[i + j] / spj[i] )
                d=d+j
                break
    if f != 0:
        for j in range(21, 45):
            if spj[i + j] > spj[i]*1.03:
                f = f * 0
                s = s *(spj[i + j] / spj[i] )
                d=d+j
                break
    if f!=0:
        s = s * (spj[i + 45] / spj[i] )
        sb.append(i)
        d=45
    n=n+f
    m=m+1
    dc=(s/s_last)**(243/d)
    dcz=dcz*dc
    ld+=d
sj1=s**((len(tr)/((len(last)-45-qs)/243))/m)
sj2=dcz**(1/m)
print("胜率：",1-n/m,"平均周转天数：",ld/m,"等效平均年化收益：",(sj1-1)*100,(sj2-1)*100)
print("应投入：",len(tr),tr)
print("失败：",len(sb),sb)
print('倍数',s,"单词几何平均",(s**(1/m)-1)*100)




